-- EXEC  SP_LOAD_WAFER_PART_PKG_DETAILS 124, '', 1, 2011

ALTER PROC SP_LOAD_WAFER_PART_PKG_DETAILS
 @PROCESSSTEP_ID VARCHAR(20),
 @PACKAGE_PARAMETER VARCHAR(50),
 @Quarter INT,
 @Year INT
 
 AS
 BEGIN
 	DECLARE @FAB_PROCESSSTEP_ID INT
 	DECLARE @PART_NUMBER VARCHAR(50)
 	DECLARE @PACKAGE_TYPE VARCHAR(50)
 	DECLARE @DIEBANK_PROCESSSTEP_ID INT 
 	DECLARE @ASSM_PROCESSSTEP_ID INT
	DECLARE @FG_PROCESSSTEP_ID INT 
 
 	DECLARE @WaferCost	NUMERIC(18,4)
 	DECLARE @BGCost		NUMERIC(18,4)
 	DECLARE @PkgCost	NUMERIC(18,4)
 	DECLARE @testTimeInSec	NUMERIC(18,4)
 	DECLARE @ROWCOUNT	INT
 	DECLARE @testCostPerSec	NUMERIC(18,4)
 	DECLARE @TRCostY	NUMERIC(18,4)
 	DECLARE @TRCostT	NUMERIC(18,4)
 	DECLARE @bulkCost	NUMERIC(18,4)
 	DECLARE @YieldAssy	NUMERIC(18,4)
 	DECLARE @YieldTest	NUMERIC(18,4)
 	DECLARE @Yield		NUMERIC(18,4)
 	DECLARE @Raw_Wafer_Cost		NUMERIC(18,4)
 	DECLARE @ZRO_EXP2_Cost		NUMERIC(18,4) 
 	DECLARE @EPI_VT_Cost		NUMERIC(18,4)
 	DECLARE @VN_EXP_Cost		NUMERIC(18,4)
 	DECLARE @Epi_Seal_Cost		NUMERIC(18,4)
 	DECLARE @Pre_Grind_Cost		NUMERIC(18,4)
 	DECLARE @Sort_Cost			NUMERIC(18,4)
 	DECLARE @Sort_Yield			NUMERIC(18,4)
 	DECLARE @BG_Yield			NUMERIC(18,4)
 	DECLARE @Mix_Match_Yield			NUMERIC(18,4)
 	DECLARE @GDW		INT
 	DECLARE @Total_Wafer_Cost	NUMERIC(18,4)
 	DECLARE @Cumm_Cost_Per_Die	NUMERIC(18,4)
 	DECLARE @CMOS_Cumm_Cost_Per_Die NUMERIC(18,4)
 	DECLARE @MEMS_Cumm_Cost_Per_Die NUMERIC(18,4)
 	DECLARE @Process_Type VARCHAR(50)
 	DECLARE @Process_parameter VARCHAR(50)
 	DECLARE @SQL_STRING VARCHAR(800)
 
 	DECLARE @RESULTS TABLE(PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50),GDW INT, PACKAGE_NAME VARCHAR(50), WaferCost	NUMERIC(18,4) DEFAULT 0.0,BGCost NUMERIC(18,4) DEFAULT 0.0,
 	PkgCost		NUMERIC(18,4) DEFAULT 0.0,	testTimeInSec	NUMERIC(18,4) DEFAULT 1.0,testCostPerSec	NUMERIC(18,4) DEFAULT 0.0,	TRCostY NUMERIC(18,4) DEFAULT 0.0,TRCostT	NUMERIC(18,4) DEFAULT 0.0,bulkCost	NUMERIC(18,4) DEFAULT 0.0,
 	YieldAssy NUMERIC(18,4) DEFAULT 0.0,YieldTest NUMERIC(18,4) DEFAULT 0.0,Yield NUMERIC(18,4) DEFAULT 0.0, Raw_Wafer_Cost NUMERIC(18,4) DEFAULT 0.0,
 	ZRO_EXP2_Cost	NUMERIC(18,4) DEFAULT 0.0, EPI_VT_Cost	NUMERIC(18,4) DEFAULT 0.0, VN_EXP_Cost	NUMERIC(18,4) DEFAULT 0.0,
 	Epi_Seal_Cost	NUMERIC(18,4) DEFAULT 0.0, Pre_Grind_Cost	NUMERIC(18,4) DEFAULT 0.0, Sort_Cost NUMERIC(18,4) DEFAULT 0.0, 
 	Sort_Yield NUMERIC(18,4) DEFAULT 0.0, Total_Wafer_Cost NUMERIC(18,4) DEFAULT 0.0, Cumm_Cost_Per_Die NUMERIC(18,4) DEFAULT 0.0, 
 	Back_Grind_Yield NUMERIC(18,4) DEFAULT 0.0, Mix_Match_Yield NUMERIC(18,4) DEFAULT 0.0, Cmos_Wafer_Cost NUMERIC(18,4) DEFAULT 0.0, MEMS_Wafer_Cost NUMERIC(18,4) DEFAULT 0.0 ) 
 
 	SELECT @Process_Type = PS_TYPE_NAME FROM Process_Step WHERE ProcessStep_ID = @PROCESSSTEP_ID
 	SELECT @Process_parameter = Parameter_Value FROM PROCESS_PARAMETER WHERE ProcessStep_Id = @PROCESSSTEP_ID AND Parameter_Key = 'IS_MEMS_WAFER'
 	IF @Process_parameter IS NOT NULL AND @Process_parameter = 'TRUE'
 	BEGIN
 		SET @Process_Type = 'MEMS'
 	END
 	
 	IF( @Process_Type = 'Die Bank' )
 	BEGIN
 		SET @SQL_STRING = 'DECLARE PKG_CURSOR CURSOR FOR select Part_Number, PS.Processstep_id  from Product P , PROCESS_STEP PS
 		WHERE P.Processstep_id = PS.Processstep_id AND PS_TYPE = ''Die Bank'' '	
 
 		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0
 		BEGIN
 			SET @SQL_STRING = @SQL_STRING +' AND PS.Processstep_id = ''' + @PROCESSSTEP_ID + ''''
 		END	
 		EXEC (@SQL_STRING)
 
 		OPEN PKG_CURSOR
 		FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @DIEBANK_PROCESSSTEP_ID
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
			SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'FIN_GDW' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB' )
			IF( @GDW IS NULL OR @GDW < 1  )
			BEGIN
				SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'Gross Die/Wafer' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB' )
			END
 			
 			SELECT @ROWCOUNT = COUNT(*) FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @DIEBANK_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 			IF @ROWCOUNT > 0
 			BEGIN
 				SELECT @WaferCost = [Wafer Cost],@BGCost= [BackGrind Cost], @Cumm_Cost_Per_Die = Cumm_Cost_Per_Die 
 				FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @DIEBANK_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 				SET @Total_Wafer_Cost = ISNULL(@WaferCost,0.0) + ISNULL(@BGCost,0.0)
 				/*
 				IF @Total_Wafer_Cost > 0.0
 				BEGIN
 					SET @Cumm_Cost_Per_Die = ISNULL(@Total_Wafer_Cost, 0.0)/@GDW
 				END
 				ELSE
 				BEGIN
 					SET @Cumm_Cost_Per_Die = 0.0
 				END*/
 				--UPDATE Wafer_Pkg_Cost_Table SET Cumm_Cost_Per_Die = @Cumm_Cost_Per_Die WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, GDW, WaferCost,BGCost, Total_Wafer_Cost, Cumm_Cost_Per_Die ) 
 				VALUES ( @DIEBANK_PROCESSSTEP_ID, @PART_NUMBER, @GDW, ISNULL(@WaferCost,0.0),ISNULL(@BGCost,0.0), ISNULL( @Total_Wafer_Cost, 0.0), ISNULL( @Cumm_Cost_Per_Die, 0.0 ) )
 			END
 			ELSE
 			BEGIN
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, GDW, WaferCost,BGCost, Total_Wafer_Cost, Cumm_Cost_Per_Die) 
 				VALUES ( @DIEBANK_PROCESSSTEP_ID, @PART_NUMBER, @GDW, 0.0,0.0, 0.0,0.0 )
 			END
 
 			SET @WaferCost	= NULL
 			SET @BGCost	= NULL
 			SET @ROWCOUNT = 0
 			SET @GDW = 0
 			SET @PART_NUMBER= NULL
 			SET @FAB_PROCESSSTEP_ID = NULL 
			SET @DIEBANK_PROCESSSTEP_ID = NULL
 			FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @DIEBANK_PROCESSSTEP_ID
 		END
 		CLOSE PKG_CURSOR
 		DEALLOCATE PKG_CURSOR
 	END
 	ELSE IF @Process_Type = 'ASSEMBLY' 
 	BEGIN
 		SET @SQL_STRING = 'DECLARE PKG_CURSOR CURSOR FOR select Part_Number, PS.Processstep_id  from Product P, PROCESS_STEP PS
 		WHERE P.Processstep_id = PS.Processstep_id AND PS_TYPE = ''ASSEMBLY'' '	
 
 		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0
 		BEGIN
 				SET @SQL_STRING = @SQL_STRING +' AND PS.Processstep_id = ''' + @PROCESSSTEP_ID + ''''
 		END	
 
 		EXEC (@SQL_STRING)	
 		
 		OPEN PKG_CURSOR
 		FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @ASSM_PROCESSSTEP_ID
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
 			SELECT @PACKAGE_TYPE = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'Package ID' AND ProcessStep_Id = @ASSM_PROCESSSTEP_ID
 			SELECT @ROWCOUNT = COUNT(*) FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @ASSM_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 			IF @ROWCOUNT > 0
 			BEGIN
 				SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ASSM_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'Die Bank'
 				SELECT @CMOS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
				SET @FAB_PROCESSSTEP_ID = null
 				SELECT @FAB_PROCESSSTEP_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_PROCESSSTEP_ID
 				--SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @FAB_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB'
 				SELECT @MEMS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				SELECT @PkgCost =[Pkg Cost],@testTimeInSec=[Test Time InSec],@testCostPerSec =[Test Cost PerSec],
 				@TRCostY=[TR CostY],@TRCostT=[TR CostT],@bulkCost=[Bulk],@YieldAssy=[Yield_Assy],@YieldTest=[Yield_Test],@Yield =[Yield], @Cumm_Cost_Per_Die = Cumm_Cost_Per_Die
 				FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @ASSM_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				/*SET @Cumm_Cost_Per_Die = ( ( ISNULL(@CMOS_Cumm_Cost_Per_Die, 0.0) + ISNULL(@MEMS_Cumm_Cost_Per_Die, 0.0) )/ (ISNULL(@YieldAssy,100.00)/100) ) + ISNULL( @PkgCost, 0.0 )
 				PRINT @Cumm_Cost_Per_Die
 				SET @Cumm_Cost_Per_Die = ( @Cumm_Cost_Per_Die + ( ISNULL(@testCostPerSec, 0.0) * ISNULL(@testTimeInSec, 0.0) ) )/(ISNULL(@YieldTest,100.00)/100) 
 				PRINT @Cumm_Cost_Per_Die
 				SET @Cumm_Cost_Per_Die = ( @Cumm_Cost_Per_Die + ISNULL( @bulkCost, 0.0) )/(ISNULL(@Yield,100.00)/100) 
 				*/
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, PACKAGE_NAME, PkgCost,testTimeInSec,testCostPerSec,	TRCostY,
 				TRCostT, bulkCost, YieldAssy, YieldTest, Yield, Cumm_Cost_Per_Die, Cmos_Wafer_Cost, MEMS_Wafer_Cost ) 
 				VALUES ( @ASSM_PROCESSSTEP_ID, @PART_NUMBER, @PACKAGE_TYPE, ISNULL(@PkgCost,0.0),ISNULL(@testTimeInSec,0),ISNULL(@testCostPerSec,0.0), ISNULL(@TRCostY,0.0),
 				ISNULL(@TRCostT,0.0),ISNULL(@bulkCost,0.0),ISNULL(@YieldAssy,0.0),ISNULL(@YieldTest,0.0) ,ISNULL(@Yield,0.0), @Cumm_Cost_Per_Die, ISNULL( @CMOS_Cumm_Cost_Per_Die, 0.0), ISNULL( @MEMS_Cumm_Cost_Per_Die, 0.0) )
 			END
 			ELSE
 			BEGIN
 				SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ASSM_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'Die Bank'
 				SELECT @CMOS_Cumm_Cost_Per_Die = Cumm_Cost_Per_Die FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
				SET @FAB_PROCESSSTEP_ID = null
 				SELECT @FAB_PROCESSSTEP_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_PROCESSSTEP_ID
 				--SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @FAB_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB'
 				SELECT @MEMS_Cumm_Cost_Per_Die = Cumm_Cost_Per_Die FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, PACKAGE_NAME, PkgCost,testTimeInSec,testCostPerSec,TRCostY,
 				TRCostT, bulkCost, YieldAssy, YieldTest, Yield, Cumm_Cost_Per_Die, Cmos_Wafer_Cost, MEMS_Wafer_Cost ) 
 				VALUES ( @ASSM_PROCESSSTEP_ID, @PART_NUMBER, @PACKAGE_TYPE, 0.0,0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0, 0.0, ISNULL(@CMOS_Cumm_Cost_Per_Die, 0.0), ISNULL(@MEMS_Cumm_Cost_Per_Die, 0.0) )
 			END
 
 			SET @PACKAGE_TYPE = NULL
 			SET @ASSM_PROCESSSTEP_ID = NULL
 			SET @WaferCost	= NULL
 			SET @BGCost	= NULL
 			SET @PkgCost	= NULL
 			SET @testTimeInSec = NULL
 			SET @testCostPerSec = NULL
 			SET @TRCostY	= NULL
 			SET @TRCostT	= NULL
 			SET @bulkCost	= NULL
 			SET @YieldAssy	= NULL
 			SET @YieldTest	= NULL
 			SET @Yield	= NULL
 			SET @ROWCOUNT = 0
 			SET @GDW = 0
 			SET @PART_NUMBER= NULL
 			SET @FAB_PROCESSSTEP_ID = NULL 
 			FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @ASSM_PROCESSSTEP_ID
 		END
 		CLOSE PKG_CURSOR
 		DEALLOCATE PKG_CURSOR
 	END
 	ELSE IF @Process_Type = 'Finished Goods' 
 	BEGIN
 		SET @SQL_STRING = 'DECLARE PKG_CURSOR CURSOR FOR select Part_Number, PS.Processstep_id  from Product P, PROCESS_STEP PS
 		WHERE P.Processstep_id = PS.Processstep_id AND PS_TYPE = ''FG'' '	
 
 		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0
 		BEGIN
 				SET @SQL_STRING = @SQL_STRING +' AND PS.Processstep_id = ''' + @PROCESSSTEP_ID + ''''
 		END	
 
 		EXEC (@SQL_STRING)	
 		
 		OPEN PKG_CURSOR
 		FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @FG_PROCESSSTEP_ID
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
			SELECT @ASSM_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @FG_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'ASSEMBLY'
 			SELECT @PACKAGE_TYPE = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'Package ID' AND ProcessStep_Id = @ASSM_PROCESSSTEP_ID
 			SELECT @ROWCOUNT = COUNT(*) FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 			IF @ROWCOUNT > 0
 			BEGIN
 				SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ASSM_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'Die Bank'
 				SELECT @CMOS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
				SET @FAB_PROCESSSTEP_ID = null
 				SELECT @FAB_PROCESSSTEP_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_PROCESSSTEP_ID
 				--SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @FAB_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB'
 				SELECT @MEMS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				SELECT @PkgCost =[Pkg Cost],@testTimeInSec=[Test Time InSec],@testCostPerSec =[Test Cost PerSec],
 				@TRCostY=[TR CostY],@TRCostT=[TR CostT],@bulkCost=[Bulk],@YieldAssy=[Yield_Assy],@YieldTest=[Yield_Test],@Yield =[Yield], @Cumm_Cost_Per_Die = Cumm_Cost_Per_Die
 				FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				/*SET @Cumm_Cost_Per_Die = ( ( ISNULL(@CMOS_Cumm_Cost_Per_Die, 0.0) + ISNULL(@MEMS_Cumm_Cost_Per_Die, 0.0) )/ (ISNULL(@YieldAssy,100.00)/100) ) + ISNULL( @PkgCost, 0.0 )
 				PRINT @Cumm_Cost_Per_Die
 				SET @Cumm_Cost_Per_Die = ( @Cumm_Cost_Per_Die + ( ISNULL(@testCostPerSec, 0.0) * ISNULL(@testTimeInSec, 0.0) ) )/(ISNULL(@YieldTest,100.00)/100) 
 				PRINT @Cumm_Cost_Per_Die
 				SET @Cumm_Cost_Per_Die = ( @Cumm_Cost_Per_Die + ISNULL( @bulkCost, 0.0) )/(ISNULL(@Yield,100.00)/100) 
 				*/
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, PACKAGE_NAME, PkgCost,testTimeInSec,testCostPerSec,	TRCostY,
 				TRCostT, bulkCost, YieldAssy, YieldTest, Yield, Cumm_Cost_Per_Die, Cmos_Wafer_Cost, MEMS_Wafer_Cost ) 
 				VALUES ( @FG_PROCESSSTEP_ID, @PART_NUMBER, @PACKAGE_TYPE, ISNULL(@PkgCost,0.0),ISNULL(@testTimeInSec,0),ISNULL(@testCostPerSec,0.0), ISNULL(@TRCostY,0.0),
 				ISNULL(@TRCostT,0.0),ISNULL(@bulkCost,0.0),ISNULL(@YieldAssy,0.0),ISNULL(@YieldTest,0.0) ,ISNULL(@Yield,0.0), @Cumm_Cost_Per_Die, ISNULL( @CMOS_Cumm_Cost_Per_Die, 0.0), ISNULL( @MEMS_Cumm_Cost_Per_Die, 0.0) )
 			END
 			ELSE
 			BEGIN
 				SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ASSM_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'Die Bank'
 				SELECT @CMOS_Cumm_Cost_Per_Die = Cumm_Cost_Per_Die FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
				SET @FAB_PROCESSSTEP_ID = null
 				SELECT @FAB_PROCESSSTEP_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_PROCESSSTEP_ID
 				--SELECT @FAB_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @FAB_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB'
 				SELECT @MEMS_Cumm_Cost_Per_Die = Cumm_Cost_Per_Die FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, PACKAGE_NAME, PkgCost,testTimeInSec,testCostPerSec,TRCostY,
 				TRCostT, bulkCost, YieldAssy, YieldTest, Yield, Cumm_Cost_Per_Die, Cmos_Wafer_Cost, MEMS_Wafer_Cost ) 
 				VALUES ( @FG_PROCESSSTEP_ID, @PART_NUMBER, @PACKAGE_TYPE, 0.0,0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0, 0.0, ISNULL(@CMOS_Cumm_Cost_Per_Die, 0.0), ISNULL(@MEMS_Cumm_Cost_Per_Die, 0.0) )
 			END
 
 			SET @PACKAGE_TYPE = NULL
 			SET @ASSM_PROCESSSTEP_ID = NULL
 			SET @WaferCost	= NULL
 			SET @BGCost	= NULL
 			SET @PkgCost	= NULL
 			SET @testTimeInSec = NULL
 			SET @testCostPerSec = NULL
 			SET @TRCostY	= NULL
 			SET @TRCostT	= NULL
 			SET @bulkCost	= NULL
 			SET @YieldAssy	= NULL
 			SET @YieldTest	= NULL
 			SET @Yield	= NULL
 			SET @ROWCOUNT = 0
 			SET @GDW = 0
 			SET @PART_NUMBER= NULL
 			SET @FAB_PROCESSSTEP_ID = NULL 
 			FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @ASSM_PROCESSSTEP_ID
 		END
 		CLOSE PKG_CURSOR
 		DEALLOCATE PKG_CURSOR
 	END
 	ELSE IF ( @Process_Type = 'MEMS' ) 
 	BEGIN
 		SET @SQL_STRING = 'DECLARE PKG_CURSOR CURSOR FOR select Part_Number, PS.Processstep_id  from Product P , PROCESS_STEP PS
 		WHERE P.Processstep_id = PS.Processstep_id AND PS_TYPE = ''Die Bank'' '	
 
 		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0
 		BEGIN
 			SET @SQL_STRING = @SQL_STRING +' AND PS.Processstep_id = ''' + @PROCESSSTEP_ID + ''''
 		END	
 		EXEC (@SQL_STRING)
 
 		OPEN PKG_CURSOR
 		FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @DIEBANK_PROCESSSTEP_ID
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
			SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'FIN_GDW' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB' )
			IF( @GDW IS NULL OR @GDW < 1  )
			BEGIN
				SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'Gross Die/Wafer' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB' )
			END
 			
 			SELECT @ROWCOUNT = COUNT(*) FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @DIEBANK_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 			IF @ROWCOUNT > 0
 			BEGIN
 				SELECT @WaferCost = [Wafer Cost],@Raw_Wafer_Cost= [Raw_Wafer_Cost],@ZRO_EXP2_Cost= [ZRO_EXP2_Cost],@EPI_VT_Cost= [EPI_VT_Cost],
 				@VN_EXP_Cost= [VN_EXP_Cost],@Epi_Seal_Cost= [Epi_Seal_Cost],@BG_Yield = [Back_Grind_Yield], @Pre_Grind_Cost= [Pre_Grind_Cost],@BGCost= [BackGrind Cost] ,@Sort_Cost= [Sort_Cost],@Sort_Yield= [Sort_Yield] , @Mix_Match_Yield = [Mix_Match_Yield], @Cumm_Cost_Per_Die = Cumm_Cost_Per_Die
 				FROM Wafer_Pkg_Cost_Table WHERE PROCESSSTEP_ID = @DIEBANK_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 
 				SET @Total_Wafer_Cost = ( ISNULL(@WaferCost,0.0) + ISNULL(@Raw_Wafer_Cost,0.0) + ISNULL( @ZRO_EXP2_Cost, 0.0) + ISNULL( @EPI_VT_Cost, 0.0) + ISNULL( @VN_EXP_Cost, 0.0) + ISNULL( @Epi_Seal_Cost, 0.0) + ISNULL( @Pre_Grind_Cost, 0.0) + ISNULL( @BGCost, 0.0) )/(@BG_Yield/100)
 				SET @Total_Wafer_Cost = ( @Total_Wafer_Cost + @Sort_Cost )/(@Sort_Yield/100)
 				SET @Total_Wafer_Cost = ( @Total_Wafer_Cost )/(@Mix_Match_Yield/100)
 
 				/*IF @Total_Wafer_Cost > 0.0
 				BEGIN
 					SET @Cumm_Cost_Per_Die = ISNULL(@Total_Wafer_Cost, 0.0)/@GDW
 				END
 				ELSE
 				BEGIN
 					SET @Cumm_Cost_Per_Die = 0.0
 				END*/
 
 				--UPDATE Wafer_Pkg_Cost_Table SET Cumm_Cost_Per_Die = @Cumm_Cost_Per_Die WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND Quarter = @Quarter AND Year = @Year
 	
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, GDW, WaferCost, Raw_Wafer_Cost, ZRO_EXP2_Cost, EPI_VT_Cost, VN_EXP_Cost, Epi_Seal_Cost, Pre_Grind_Cost,BGCost, Sort_Cost, Sort_Yield, Back_Grind_Yield, Mix_Match_Yield, Total_Wafer_Cost, Cumm_Cost_Per_Die ) 
 				VALUES ( @DIEBANK_PROCESSSTEP_ID, @PART_NUMBER, @GDW, ISNULL( @WaferCost, 0.0), ISNULL( @Raw_Wafer_Cost, 0.0), ISNULL( @ZRO_EXP2_Cost, 0.0), ISNULL( @EPI_VT_Cost, 0.0), ISNULL( @VN_EXP_Cost, 0.0), ISNULL( @Epi_Seal_Cost, 0.0), ISNULL( @Pre_Grind_Cost, 0.0), ISNULL(@BGCost, 0.0), ISNULL( @Sort_Cost, 0.0), ISNULL( @Sort_Yield, 0.0), ISNULL( @BG_Yield, 0.0), ISNULL( @Mix_Match_Yield, 0.0 ), @Total_Wafer_Cost, @Cumm_Cost_Per_Die )
 			END
 			ELSE
 			BEGIN
 				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER, GDW, WaferCost, Raw_Wafer_Cost, ZRO_EXP2_Cost, EPI_VT_Cost, VN_EXP_Cost, Epi_Seal_Cost, Pre_Grind_Cost, BGCost, Sort_Cost, Sort_Yield, Back_Grind_Yield, Mix_Match_Yield, Total_Wafer_Cost, Cumm_Cost_Per_Die ) 
 				VALUES ( @DIEBANK_PROCESSSTEP_ID, @PART_NUMBER, @GDW, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 )
 			END
 
 			SET @WaferCost	= NULL
 			SET @BGCost	= NULL
 			SET @ROWCOUNT = 0
 			SET @GDW = 0
 			SET @PART_NUMBER= NULL
 			SET @FAB_PROCESSSTEP_ID = NULL 
 			FETCH NEXT FROM PKG_CURSOR INTO @PART_NUMBER, @DIEBANK_PROCESSSTEP_ID
 		END
 		CLOSE PKG_CURSOR
 		DEALLOCATE PKG_CURSOR
 	END
 	SELECT * FROM @RESULTS
 END

